package com.apobates.jforum2.troll.threads.biz.impl.dao;

import com.apobates.jforum2.troll.regular.ForumEntityStatusEnum;
import com.apobates.jforum2.troll.threads.biz.dao.*;
import com.apobates.jforum2.troll.threads.entity.Album;
import com.apobates.jforum2.troll.threads.entity.Topic;
import com.apobates.jforum2.troll.threads.entity.TopicCategory;
import com.apobates.jforum2.troll.threads.entity.TopicConfig;
import com.apobates.jforum2.troll.threads.entity.TopicTag;
import com.apobates.jforum2.troll.utils.core.Commons;
import com.apobates.jforum2.troll.utils.persistence.Page;
import com.apobates.jforum2.troll.utils.persistence.Pageable;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.TreeMap;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author xiaofanku
 * @since 20200513
 */
@Repository
public class TopicDaoImpl implements TopicDao{
    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private TopicTagDao topicTagDao;
    @Autowired
    private TopicConfigDao topicConfigDao;
    @Autowired
    private TopicStatsDao topicStatsDao;
    @Autowired
    private AlbumDao albumDao;
    private final static Logger logger = LoggerFactory.getLogger(TopicDaoImpl.class);

    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public Optional<Boolean> editStatus(long id, ForumEntityStatusEnum status) {
        try {
            int affect = entityManager.createQuery("UPDATE Topic t SET t.status = ?1 WHERE t.id = ?2").setParameter(1, status).setParameter(2, id).executeUpdate();
            return (affect == 1) ? Optional.of(true) : Optional.empty();
        } catch (Exception e) {
            return Optional.empty();
        }
    }
    //分离@20210414
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public void modifyTopic(long topicId, String title, Album album, Set<TopicTag> tags)throws IllegalStateException {
        int affect = entityManager.createQuery("UPDATE Topic t SET t.title = ?1 WHERE t.id = ?2").setParameter(1, title).setParameter(2, topicId).executeUpdate();
        if(affect!=1){
            throw new IllegalStateException("话题标题编辑失败");
        }
        if(null!=album){
            //先移除
            try{
                albumDao.cascadeDel(topicId, true);
            }catch(IllegalStateException e){
                if (logger.isDebugEnabled()) {
                    logger.debug("[TopicDao][removeAlbum]获取话题的像册时失败", e);
                }
            }
            //再增加
            albumDao.cascadeSave(album, album.getPictures());
        }
        //
        if(null!=tags && !tags.isEmpty()){
            topicTagDao.batchSave(topicId, tags);
        }
        
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public Optional<Boolean> editTop(long id, boolean topValue) {
        try {
            int affect = entityManager.createQuery("UPDATE Topic t SET t.tops = ?1 WHERE t.id = ?2").setParameter(1, topValue).setParameter(2, id).executeUpdate();
            return (affect == 1) ? Optional.of(true) : Optional.empty();
        } catch (Exception e) {
            return Optional.empty();
        }
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public Optional<Boolean> editGoods(long id, boolean goodsValue) {
        try {
            int affect = entityManager.createQuery("UPDATE Topic t SET t.goods = ?1 WHERE t.id = ?2").setParameter(1, goodsValue).setParameter(2, id).executeUpdate();
            return (affect == 1) ? Optional.of(true) : Optional.empty();
        } catch (Exception e) {
            return Optional.empty();
        }
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    public Optional<Boolean> editAlbum(long id, long albumId) {
        int affectRows = entityManager.createQuery("UPDATE Topic t SET t.albumId = ?1 WHERE t.id = ?2").setParameter(1, albumId).setParameter(2, id).executeUpdate();
        return affectRows == 1 ? Optional.of(true) : Optional.empty();
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public Optional<Boolean> updateRankingDate(long id, LocalDateTime updateDateTime) {
        int affectRows = entityManager.createQuery("UPDATE Topic t SET t.rankingDateTime = ?1 WHERE t.id = ?2").setParameter(1, updateDateTime).setParameter(2, id).executeUpdate();
        return affectRows == 1 ? Optional.of(true) : Optional.empty();
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public boolean removeAlbum(long id)throws IllegalStateException {
        int affectTopicAlbum = entityManager.createQuery("UPDATE Topic t SET t.albumId = ?1 WHERE t.id = ?2").setParameter(1, 0).setParameter(2, id).executeUpdate();
        
        if(affectTopicAlbum != 1){
            throw new IllegalStateException("移除话题的像册失败");
        }
        //移至AlbumDao
        try{
            albumDao.cascadeDel(id, true);
        }catch(IllegalStateException e){
           if (logger.isDebugEnabled()) {
               logger.debug("[TopicDao][removeAlbum]获取话题的像册时失败", e);
           }
        }
        return true;
    }
    //分离@20210414
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public void moveTopic(long id, long targetBoardId, int targetBoardGroupId)throws IllegalStateException {
        //设置话题的版块
        try {
            entityManager.createQuery("UPDATE Topic t SET t.boardId = ?1, t.volumesId = ?2 WHERE t.id = ?3")
                    .setParameter(1, targetBoardId)
                    .setParameter(2, targetBoardGroupId)
                    .setParameter(3, id)
                    .executeUpdate();
        } catch (Exception e) {
            logger.debug("[MTV][4]更新话题到目标版块失败, 异常: " + e.getMessage());
            throw new IllegalStateException("更新话题到目标版块失败, 异常: " + e.getMessage());
        }
        //更新话题统计的版块
        try {
            entityManager.createQuery("UPDATE TopicStats ts SET ts.boardId = ?1, ts.volumesId = ?2 WHERE ts.topicId = ?3")
                    .setParameter(1, targetBoardId)
                    .setParameter(2, targetBoardGroupId)
                    .setParameter(3, id)
                    .executeUpdate();
        } catch (Exception e) {
            logger.debug("[MTV][6]更新话题统计为目标版块失败, 异常: " + e.getMessage());
            throw new IllegalStateException("更新话题统计为目标版块失败, 异常: " + e.getMessage());
        }
        //话题回复
        //跨服务事务@20210413
        //更新话题像册的版块
        try {
            entityManager.createQuery("UPDATE Album pa SET pa.boardId = ?1, pa.volumesId = ?2 WHERE pa.topicId = ?3")
                    .setParameter(1, targetBoardId)
                    .setParameter(2, targetBoardGroupId)
                    .setParameter(3, id)
                    .executeUpdate();
        } catch (Exception e) {
            logger.debug("[MTV][10]更新话题像册为目标版块失败, 异常: " + e.getMessage());
            throw new IllegalStateException("更新话题像册为目标版块失败, 异常: " + e.getMessage());
        }
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public Optional<Boolean> removeTermArticle(long id, long termId) {
        try {
            int affect = entityManager.createQuery("UPDATE Topic t SET t.status = ?1 WHERE t.id = ?2 AND t.boardId = ?3 AND t.suggest = ?4").setParameter(1, ForumEntityStatusEnum.DELETE).setParameter(2, id).setParameter(3, termId).setParameter(4, false).executeUpdate();
            return affect == 1 ? Optional.of(true) : Optional.empty();
        } catch (Exception e) {
            return Optional.empty();
        }
    }
    
    @Override
    public Page<Topic> findAllByBoard(long boardId, Pageable pageable) {
        final long total = findAllByBoardCount(boardId);
        if (total == 0) {
            return emptyResult();
        }
        TypedQuery<Topic> query = entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 AND t.tops = ?2 AND t.status != ?3 AND t.article = ?4 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, boardId)
                .setParameter(2, false)
                .setParameter(3, ForumEntityStatusEnum.DELETE)
                .setParameter(4, false);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        
        final Stream<Topic> result = query.getResultStream();
        return new Page<Topic>() {
            @Override
            public long getTotalElements() {
                return total;
            }
            
            @Override
            public Stream<Topic> getResult() {
                return result;
            }
        };
    }
    
    private long findAllByBoardCount(long boardId) {
        try {
            return entityManager.createQuery("SELECT COUNT(t) FROM Topic t WHERE t.boardId = ?1 AND t.tops = ?2 AND t.status != ?3 AND t.article = ?4", Long.class)
                    .setParameter(1, boardId)
                    .setParameter(2, false)
                    .setParameter(3, ForumEntityStatusEnum.DELETE)
                    .setParameter(4, false)
                    .getSingleResult();
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[findAllByBoardCount][TopicDao]", e);
            }
        }
        return 0L;
    }
    
    @Override
    public Page<Topic> findAllByBoard(long boardId, String categoryValue, Pageable pageable) {
        final long total = findAllByBoardCount(boardId, categoryValue);
        if (total == 0) {
            return emptyResult();
        }
        TypedQuery<Topic> query = entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 AND t.tops = ?2 AND t.status != ?3 AND t.topicCategoryValue = ?4 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, boardId)
                .setParameter(2, false)
                .setParameter(3, ForumEntityStatusEnum.DELETE)
                .setParameter(4, categoryValue);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        
        final Stream<Topic> result = query.getResultStream();
        return new Page<Topic>() {
            @Override
            public long getTotalElements() {
                return total;
            }
            
            @Override
            public Stream<Topic> getResult() {
                return result;
            }
        };
    }
    
    private long findAllByBoardCount(long boardId, String categoryValue) {
        try {
            return entityManager.createQuery("SELECT COUNT(t) FROM Topic t WHERE t.boardId = ?1 AND t.tops = ?2 AND t.status != ?3 AND t.topicCategoryValue = ?4", Long.class)
                    .setParameter(1, boardId)
                    .setParameter(2, false)
                    .setParameter(3, ForumEntityStatusEnum.DELETE)
                    .setParameter(4, categoryValue)
                    .getSingleResult();
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[findAllByBoardCount][TopicDao]", e);
            }
        }
        return 0L;
    }

    @Override
    public Page<Topic> findAllTopsForBoard(long boardId, Pageable pageable) {
        final long total = findAllTopsForBoardCount(boardId);
        if (total == 0) {
            return emptyResult();
        }
        TypedQuery<Topic> query = entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 AND t.tops = ?2 AND t.status != ?3 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, boardId)
                .setParameter(2, true)
                .setParameter(3, ForumEntityStatusEnum.DELETE);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());

        final Stream<Topic> result = query.getResultStream();
        return new Page<Topic>() {
            @Override
            public long getTotalElements() {
                return total;
            }

            @Override
            public Stream<Topic> getResult() {
                return result;
            }
        };
    }
    private long findAllTopsForBoardCount(long boardId) {
        try {
            return entityManager.createQuery("SELECT COUNT(t) FROM Topic t WHERE t.boardId = ?1 AND t.tops = ?2 AND t.status != ?3", Long.class)
                    .setParameter(1, boardId)
                    .setParameter(2, true)
                    .setParameter(3, ForumEntityStatusEnum.DELETE)
                    .getSingleResult();
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[findAllTopsForBoardCount][TopicDao]", e);
            }
        }
        return 0L;
    }

    @Override
    public Page<Topic> findAllGoodsForBoard(long boardId, Pageable pageable) {
        final long total = findAllGoodsForBoardCount(boardId);
        if (total == 0) {
            return emptyResult();
        }
        TypedQuery<Topic> query = entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 AND t.goods = ?2 AND t.status != ?3 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, boardId)
                .setParameter(2, true)
                .setParameter(3, ForumEntityStatusEnum.DELETE);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());

        final Stream<Topic> result = query.getResultStream();
        return new Page<Topic>() {
            @Override
            public long getTotalElements() {
                return total;
            }

            @Override
            public Stream<Topic> getResult() {
                return result;
            }
        };
    }
    private long findAllGoodsForBoardCount(long boardId) {
        try {
            return entityManager.createQuery("SELECT COUNT(t) FROM Topic t WHERE t.boardId = ?1 AND t.goods = ?2 AND t.status != ?3", Long.class)
                    .setParameter(1, boardId)
                    .setParameter(2, true)
                    .setParameter(3, ForumEntityStatusEnum.DELETE)
                    .getSingleResult();
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[findAllGoodsForBoardCount][TopicDao]", e);
            }
        }
        return 0L;
    }

    @Override
    public Page<Topic> findAllHotsForBoard(long boardId, Pageable pageable) {
        final long total = findAllHotsForBoardCount(boardId);
        if (total == 0) {
            return emptyResult();
        }
        TypedQuery<Topic> query = entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 AND t.hots = ?2 AND t.status != ?3 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, boardId)
                .setParameter(2, true)
                .setParameter(3, ForumEntityStatusEnum.DELETE);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());

        final Stream<Topic> result = query.getResultStream();
        return new Page<Topic>() {
            @Override
            public long getTotalElements() {
                return total;
            }

            @Override
            public Stream<Topic> getResult() {
                return result;
            }
        };
    }
    private long findAllHotsForBoardCount(long boardId) {
        try {
            return entityManager.createQuery("SELECT COUNT(t) FROM Topic t WHERE t.boardId = ?1 AND t.hots = ?2 AND t.status != ?3", Long.class)
                    .setParameter(1, boardId)
                    .setParameter(2, true)
                    .setParameter(3, ForumEntityStatusEnum.DELETE)
                    .getSingleResult();
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[findAllHotsForBoardCount][TopicDao]", e);
            }
        }
        return 0L;
    }

    @Override
    public Page<Topic> findAllByMember(long memberId, Pageable pageable) {
        final long total = findAllByMemberCount(memberId);
        if (total == 0) {
            return emptyResult();
        }
        TypedQuery<Topic> query = entityManager.createQuery("SELECT t FROM Topic t WHERE t.memberId = ?1 ORDER BY t.rankingDateTime DESC", Topic.class).setParameter(1, memberId);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        
        final Stream<Topic> result = query.getResultStream();
        return new Page<Topic>() {
            @Override
            public long getTotalElements() {
                return total;
            }
            
            @Override
            public Stream<Topic> getResult() {
                return result;
            }
        };
    }
    
    @Override
    public long findAllByMemberCount(long memberId) {
        try {
            return entityManager.createQuery("SELECT COUNT(t) FROM Topic t WHERE t.memberId = ?1", Long.class).setParameter(1, memberId).getSingleResult();
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[findAllByMemberCount][TopicDao]", e);
            }
        }
        return 0L;
    }
    
    @Override
    public Page<Topic> findAllTermArticle(long termId, Pageable pageable) {
        final long total = findAllByArticleCount(termId);
        if (total == 0) {
            return emptyResult();
        }
        TypedQuery<Topic> query = entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 AND t.suggest = ?2 AND t.status != ?3 AND t.article = ?4 ORDER BY t.entryDateTime DESC", Topic.class)
                .setParameter(1, termId)
                .setParameter(2, false)
                .setParameter(3, ForumEntityStatusEnum.DELETE)
                .setParameter(4, true);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        
        final Stream<Topic> result = query.getResultStream();
        return new Page<Topic>() {
            @Override
            public long getTotalElements() {
                return total;
            }
            
            @Override
            public Stream<Topic> getResult() {
                return result;
            }
        };
    }
    
    private long findAllByArticleCount(long termId) {
        try {
            return entityManager.createQuery("SELECT COUNT(t) FROM Topic t WHERE t.boardId = ?1 AND t.suggest = ?2 AND t.status != ?3 AND t.article = ?4", Long.class)
                    .setParameter(1, termId)
                    .setParameter(2, false)
                    .setParameter(3, ForumEntityStatusEnum.DELETE)
                    .setParameter(4, true)
                    .getSingleResult();
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[findAllByArticleCount][TopicDao]", e);
            }
        }
        return 0L;
    }
    /**
     * [NativeSQL]
     * @param tagNames
     * @param pageable
     * @return 
     */
    @Override
    public Page<Topic> findAllByTag(Collection<String> tagNames, Pageable pageable) {
        if (null == tagNames || tagNames.isEmpty()) {
            return emptyResult();
        }
        //删除掉HTML标签
        List<String> params = tagNames.stream().map(s -> Commons.htmlPurifier(s)).collect(Collectors.toList());
        //转换成SQL参数形式
        String tagNameParams = Commons.quoteSQLParameters(params).orElse(null);
        final long total = findAllByTagCount(tagNameParams);
        if (total == 0) {
            return emptyResult();
        }
        final String SQL = "SELECT t.* FROM apo_topic AS t WHERE t.SUGGEST = ?1 AND t.ARTICLE = ?2 AND t.STATUS != ?3 AND EXISTS(SELECT 1 FROM apo_topic_tag AS tt WHERE tt.`NAMES` IN(" + tagNameParams + ") AND t.ID = tt.TOPICID) ORDER BY t.RANKINGDATETIME DESC";
        Query query = entityManager.createNativeQuery(SQL, Topic.class)
                .setParameter(1, 1)
                .setParameter(2, 0)
                .setParameter(3, "DELETE");
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        @SuppressWarnings("unchecked")
        final Stream<Topic> result = query.getResultStream();
        return new Page<Topic>() {
            @Override
            public long getTotalElements() {
                return total;
            }
            
            @Override
            public Stream<Topic> getResult() {
                return result;
            }
        };
    }
    //????
    private long findAllByTagCount(String tagNameParams) {
        if (null == tagNameParams) {
            return 0L;
        }
        try {
            return entityManager.createQuery("SELECT COUNT(tt) FROM TopicTag tt WHERE tt.names IN(" + tagNameParams + ")", Long.class)
                    .getSingleResult();
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[findAllByTagCount][TopicDao]", e);
            }
        }
        return 0L;
    }
    
    @Override
    public Page<Topic> findAllByDate(LocalDateTime start, LocalDateTime finish, Pageable pageable) {
        final long total = findAllByDateCount(start,finish);
        if (total == 0) {
            return emptyResult();
        }
        final String SQL = "SELECT t FROM Topic AS t WHERE t.entryDateTime BETWEEN ?1 AND ?2 AND t.topicCategoryValue NOT IN ?3 AND t.status != ?4 AND t.suggest = ?5 AND t.article = ?6 ORDER BY t.RANKINGDATETIME DESC";
        TypedQuery<Topic> query = entityManager.createQuery(SQL, Topic.class)
                    .setParameter(1, start)
                    .setParameter(2, finish)
                    .setParameter(3, TopicCategory.isForbidCategoryValue())
                    .setParameter(4, ForumEntityStatusEnum.DELETE)
                    .setParameter(5, true)
                    .setParameter(6, false);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        
        final Stream<Topic> result = query.getResultStream();
        return new Page<Topic>() {
            @Override
            public long getTotalElements() {
                return total;
            }
            
            @Override
            public Stream<Topic> getResult() {
                return result;
            }
        };
    }
    private long findAllByDateCount(LocalDateTime start, LocalDateTime finish) {
        try {
            return entityManager.createQuery("SELECT COUNT(t) FROM Topic t WHERE t.entryDateTime BETWEEN ?1 AND ?2 AND t.topicCategoryValue NOT IN ?3 AND t.status != ?4 AND t.suggest = ?5 AND t.article = ?6", Long.class)
                    .setParameter(1, start)
                    .setParameter(2, finish)
                    .setParameter(3, TopicCategory.isForbidCategoryValue())
                    .setParameter(4, ForumEntityStatusEnum.DELETE)
                    .setParameter(5, true)
                    .setParameter(6, false)
                    .getSingleResult();
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[findAllByDateCount][TopicDao]", e);
            }
        }
        return 0L;
    }
    
    @Override
    public Stream<Topic> findAllByBoard(long boardId, int size) {
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 AND t.tops = ?2 AND t.status != ?3 AND t.suggest = ?4 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, boardId)
                .setParameter(2, false)
                .setParameter(3, ForumEntityStatusEnum.DELETE)
                .setParameter(4, true)
                .setMaxResults(size)
                .getResultStream();
    }
    
    @Override
    public Stream<Topic> findAllByBoardIgnoreStatus(long boardId, int size) {
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 ORDER BY t.entryDateTime DESC", Topic.class)
                .setParameter(1, boardId)
                .setMaxResults(size)
                .getResultStream();
    }
    
    @Override
    public Stream<Topic> findAllRecentByBoard(long boardId, LocalDateTime prevDate) {
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 AND t.entryDateTime BETWEEN ?2 AND ?3 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, boardId)
                .setParameter(2, prevDate)
                .setParameter(3, LocalDateTime.now())
                .getResultStream();
    }

    @Override
    public long findAllRecentByBoardSize(long boardId, LocalDateTime prevDate) {
        return entityManager.createQuery("SELECT COUNT(t) FROM Topic t WHERE t.boardId = ?1 AND t.entryDateTime BETWEEN ?2 AND ?3", Long.class)
                .setParameter(1, boardId)
                .setParameter(2, prevDate)
                .setParameter(3, LocalDateTime.now())
                .getSingleResult();
    }

    @Override
    public Stream<Topic> findAllByBoardOfTop(long boardId) {
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 AND t.tops = ?2 AND t.status != ?3 ORDER BY t.id DESC", Topic.class)
                .setParameter(1, boardId)
                .setParameter(2, true)
                .setParameter(3, ForumEntityStatusEnum.DELETE)
                .setMaxResults(5)
                .getResultStream();
    }
    
    @Override
    public Stream<Topic> findAllByMember(long memberId, int size) {
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.memberId = ?1 AND t.status != ?2 AND t.suggest = ?3 AND t.anony = ?4 ORDER BY t.entryDateTime DESC", Topic.class)
                .setParameter(1, memberId)
                .setParameter(2, ForumEntityStatusEnum.DELETE)
                .setParameter(3, true)
                .setParameter(4, false)
                .setMaxResults(size)
                .getResultStream();
    }

    /**
     * [NativeSQL]
     * @param memberId
     * @param size
     * @return 
     */
    @Override
    @SuppressWarnings("unchecked")
    public Stream<Topic> findAllByMemberPopular(long memberId, int size) {
        final String SQL = "SELECT t.* FROM apo_topic AS t JOIN apo_topic_stats AS ts ON t.ID = ts.TOPICID WHERE t.MEMBERID = ?1 AND ts.DISPLAIES > 1 ORDER BY ts.DISPLAIES DESC";
        return entityManager.createNativeQuery(SQL, Topic.class).setParameter(1, memberId).setMaxResults(size).getResultStream();
    }
    
    @Override
    public Stream<Topic> findAllOfRecent(int size) {
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.topicCategoryValue NOT IN ?1 AND t.status != ?2 AND t.suggest = ?3 AND t.article = ?4 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, TopicCategory.isForbidCategoryValue())
                .setParameter(2, ForumEntityStatusEnum.DELETE)
                .setParameter(3, true)
                .setParameter(4, false)
                .setMaxResults(size)
                .getResultStream();
    }
    
    @Override
    public Stream<Topic> findAllOfRecentIgnoreCondition(int size) {
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.suggest = ?1 AND t.article = ?2 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, true)
                .setParameter(2, false)
                .setMaxResults(size)
                .getResultStream();
    }
    
    @Override
    public Stream<Topic> findAllOfRecent(int boardGroupId, int size) {
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.volumesId = ?1 AND t.status != ?2 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, boardGroupId)
                .setParameter(2, ForumEntityStatusEnum.DELETE)
                .setMaxResults(size)
                .getResultStream();
    }
    
    @Override
    public Stream<Topic> findAllByBoardGroupOfGoods(int boardGroupId, int size) {
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.volumesId = ?1 AND t.status != ?2 AND t.goods = ?3 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, boardGroupId)
                .setParameter(2, ForumEntityStatusEnum.DELETE)
                .setParameter(3, true)
                .setMaxResults(size)
                .getResultStream();
    }
    
    @Override
    public Stream<Topic> findAllByReplyDate(int size) {
        return entityManager.createQuery("SELECT t FROM Topic t ORDER BY t.rankingDateTime DESC", Topic.class).setMaxResults(size).getResultStream();
    }
    
    @Override
    public Stream<Topic> findAllByBoard(long boardId, ForumEntityStatusEnum status) {
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.status = ?1 AND t.boardId = ?2 ORDER BY t.entryDateTime ASC", Topic.class)
                .setParameter(1, status)
                .setParameter(2, boardId)
                .getResultStream();
    }
    
    @Override
    public Stream<Topic> findAllOfRecentTermArticle(int size) {
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.suggest = ?1 AND t.article = ?2 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, false)
                .setParameter(2, true)
                .setMaxResults(size)
                .getResultStream();
    }
    /**
     * [NativeSQL]
     * @param boardGroupId
     * @param size
     * @return 
     */
    @Override
    public List<Topic> findAllByBoardGroupByMaxReply(int boardGroupId, int size) {
        @SuppressWarnings("unchecked")
        List<Object[]> result = entityManager.createNativeQuery("SELECT ts.TOPICID,ts.POSTSES FROM apo_topic_stats AS ts WHERE ts.VOLUMESID = ?1 AND ts.POSTSES > ?2 ORDER BY POSTSES DESC")
                .setParameter(1, boardGroupId)
                .setParameter(2, 1)
                .setMaxResults(size)
                .getResultList();
        Map<Long, Long> data = new HashMap<>();
        for (Object[] columns : result) {
            data.put((Long) columns[0], (Long) columns[1]);
        }
        if (data.isEmpty()) {
            return Collections.emptyList();
        }
        List<Topic> rs = entityManager.createQuery("SELECT t FROM Topic t WHERE t.id IN ?1 AND t.status != ?2 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, data.keySet())
                .setParameter(2, ForumEntityStatusEnum.DELETE)
                .getResultList();
        for (Topic t : rs) {
            t.setRanking(data.get(t.getId()).intValue());
        }
        return rs;
    }
    /**
     * [NativeSQL]
     * @param size
     * @return 
     */
    @Override
    public List<Topic> findAllByMaxReply(int size) {
        @SuppressWarnings("unchecked")
        List<Object[]> result = entityManager.createNativeQuery("SELECT ts.TOPICID,ts.POSTSES FROM apo_topic_stats AS ts WHERE ts.POSTSES > ?1 ORDER BY POSTSES DESC")
                .setParameter(1, 1)
                .setMaxResults(size)
                .getResultList();
        Map<Long, Long> data = new HashMap<>();
        for (Object[] columns : result) {
            data.put((Long) columns[0], (Long) columns[1]);
        }
        if (data.isEmpty()) {
            return Collections.emptyList();
        }
        List<Topic> rs = entityManager.createQuery("SELECT t FROM Topic t WHERE t.id IN ?1 AND t.status != ?2 ORDER BY t.rankingDateTime DESC", Topic.class)
                .setParameter(1, data.keySet())
                .setParameter(2, ForumEntityStatusEnum.DELETE)
                .getResultList();
        for (Topic t : rs) {
            t.setRanking(data.get(t.getId()).intValue());
        }
        return rs;
    }
    
    @Override
    public List<Topic> findAllById(Collection<Long> idList) {
        if (idList == null || idList.isEmpty()) {
            return Collections.emptyList();
        }
        return entityManager.createQuery("SELECT t FROM Topic t WHERE t.id IN ?1", Topic.class).setParameter(1, idList).getResultList();
    }
    
    @Override
    public Topic findOneById(long id) {
        return entityManager.find(Topic.class, id);
    }

    @Override
    public Optional<Topic> findNextTermArticle(long termId, long articleId) {
        try {
            Topic t = entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 AND t.id > ?2 AND t.status != ?3 ORDER BY t.id ASC", Topic.class)
                    .setParameter(1, termId)
                    .setParameter(2, articleId)
                    .setParameter(3, ForumEntityStatusEnum.DELETE)
                    .setMaxResults(1)
                    .getSingleResult();
            return Optional.ofNullable(t);
        } catch (javax.persistence.NoResultException e) {
            return Optional.empty();
        }
    }
    
    @Override
    public Optional<Topic> findPrevTermArticle(long termId, long articleId) {
        try {
            Topic t = entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 AND t.id < ?2 AND t.status != ?3 ORDER BY t.id DESC", Topic.class)
                    .setParameter(1, termId)
                    .setParameter(2, articleId)
                    .setParameter(3, ForumEntityStatusEnum.DELETE)
                    .setMaxResults(1)
                    .getSingleResult();
            return Optional.ofNullable(t);
        } catch (javax.persistence.NoResultException e) {
            return Optional.empty();
        }
    }
    
    @Override
    public Map<Long, Long> statsBoardTopicSize(LocalDateTime start, LocalDateTime finish) {
        final String SQL = "SELECT t.boardId, COUNT(t) FROM Topic t WHERE t.entryDateTime BETWEEN ?1 AND ?2 GROUP BY t.boardId";
        @SuppressWarnings("unchecked")
        List<Object[]> result = entityManager.createQuery(SQL).setParameter(1, start).setParameter(2, finish).getResultList();
        //
        Map<Long, Long> data = new HashMap<>();
        for (Object[] columns : result) {
            Long k = 0L;
            try {
                k = ((BigDecimal) columns[0]).longValue();
            } catch (java.lang.ClassCastException e) {
                k = (Long) columns[0];
            }
            Long v = 0L;
            try {
                v = ((BigDecimal) columns[1]).longValue();
            } catch (java.lang.ClassCastException e) {
                v = (Long) columns[1];
            }
            if (k == null || k == 0) {
                continue;
            }
            if (v == null) {
                continue;
            }
            data.put(k, v);
        }
        return data;
    }
    
    @Override
    public TreeMap<String, Long> groupTopicSize(LocalDateTime start, LocalDateTime finish) {
        final String SQL = "SELECT FUNCTION('DATE', t.entryDateTime), COUNT(t) FROM Topic t WHERE t.entryDateTime BETWEEN ?1 AND ?2 GROUP BY FUNCTION('DATE', t.entryDateTime)";
        @SuppressWarnings("unchecked")
        List<Object[]> result = entityManager.createQuery(SQL).setParameter(1, start).setParameter(2, finish).getResultList();
        //
        TreeMap<String, Long> data = new TreeMap<>();
        for (Object[] columns : result) {
            String k = null;
            try {
                k = (String) columns[0];
            } catch (java.lang.ClassCastException e) {
                k = columns[0].toString();
            }
            Long v = 0L;
            try {
                v = ((BigDecimal) columns[1]).longValue();
            } catch (java.lang.ClassCastException e) {
                v = (Long) columns[1];
            }
            if (k == null || v == null) {
                continue;
            }
            data.put(k, v);
        }
        return data;
    }
    
    @Override
    public long statsBoardTopicSize(long boardId, LocalDateTime start, LocalDateTime finish) {
        final String SQL = "SELECT COUNT(t) FROM Topic t WHERE t.entryDateTime BETWEEN ?1 AND ?2 AND t.boardId = ?3";
        try {
            return entityManager.createQuery(SQL, Long.class).setParameter(1, start).setParameter(2, finish).setParameter(3, boardId).getSingleResult();
        } catch (javax.persistence.NoResultException e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[statsBoardTopicSize][TopicDao]", e);
            }
        }
        return 0L;
    }
    
    @Override
    public Map<ForumEntityStatusEnum, Long> groupTopicesForStatus() {
        final String SQL = "SELECT t.status, COUNT(t) FROM Topic t GROUP BY t.status";
        @SuppressWarnings("unchecked")
        List<Object[]> result = entityManager.createQuery(SQL).getResultList();
        //
        Map<ForumEntityStatusEnum, Long> data = new HashMap<>();
        for (Object[] columns : result) {
            ForumEntityStatusEnum k = null;
            try {
                k = (ForumEntityStatusEnum) columns[0];
            } catch (java.lang.ClassCastException e) {
            }
            Long v = 0L;
            try {
                v = ((BigDecimal) columns[1]).longValue();
            } catch (java.lang.ClassCastException e) {
                v = (Long) columns[1];
            }
            if (k == null || v == null) {
                continue;
            }
            data.put(k, v);
        }
        return data;
    }
    
    @Override
    public Map<String, Long> groupTopicesForCategory() {
        final String SQL = "SELECT t.topicCategoryName, COUNT(t) FROM Topic t GROUP BY t.topicCategoryName";
        @SuppressWarnings("unchecked")
        List<Object[]> result = entityManager.createQuery(SQL).getResultList();
        //
        Map<String, Long> data = new HashMap<>();
        for (Object[] columns : result) {
            String k = null;
            try {
                k = (String) columns[0];
            } catch (java.lang.ClassCastException e) {
            }
            Long v = 0L;
            try {
                v = ((BigDecimal) columns[1]).longValue();
            } catch (java.lang.ClassCastException e) {
                v = (Long) columns[1];
            }
            if (k == null || v == null) {
                continue;
            }
            data.put(k, v);
        }
        return data;
    }
    
    @Override
    public Map<Long, Long> groupTopicesForBoard() {
        final String SQL = "SELECT t.boardId, COUNT(t) FROM Topic t GROUP BY t.boardId";
        @SuppressWarnings("unchecked")
        List<Object[]> result = entityManager.createQuery(SQL).getResultList();
        //
        Map<Long, Long> data = new HashMap<>();
        for (Object[] columns : result) {
            Long k = 0L;
            try {
                k = ((BigDecimal) columns[0]).longValue();
            } catch (java.lang.ClassCastException e) {
                k = (Long) columns[0];
            }
            Long v = 0L;
            try {
                v = ((BigDecimal) columns[1]).longValue();
            } catch (java.lang.ClassCastException e) {
                v = (Long) columns[1];
            }
            if (k == null || k == 0) {
                continue;
            }
            if (v == null) {
                continue;
            }
            data.put(k, v);
        }
        return data;
    }
    
    @Override
    public Page<Topic> findAll(Pageable pageable) {
        return emptyResult();
    }
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public void save(Topic entity) {
        entityManager.persist(entity);
    }
    

    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public long pushTopic(Topic entity, Album album, TopicConfig config) {
        //缺少更新版块统计：{另一个模块}apo_board_stats@20210919
        entityManager.persist(entity);
        entityManager.flush();
        if (entity.getId() <= 0) {
            return -1L;
        }
        //话题配置文件
        //ADD xiaofank@20210302:TopicConfig 读取失败
        TopicConfig tc = Optional.ofNullable(config).orElse(TopicConfig.defaultConfig(entity.getId()));
        if(tc.getTopicId() == 0) {
            config.setTopicId(entity.getId());
        }
        topicConfigDao.save(tc);
        //话题像册
        if(null != album){
            album.setTopicId(entity.getId());
            albumDao.cascadeSave(album, album.getPictures());
            if(album.getId()>0){
                //更新话题的像册ID
                editAlbum(entity.getId(), album.getId());
            }
            //tr.setAlbum(album);
        }
        //话题统计
        //ADD xiaofank@20210919:apo_topic_stats
        Optional<Boolean> stats = topicStatsDao.createStats(entity.getId(), entity.getVolumesId(), entity.getBoardId(), entity.getMemberId(), entity.getMemberNickname());
        if(!stats.isPresent()){
            throw new IllegalStateException("话题统计创建失败");
        }
        //return Optional.of(tr);
        return entity.getId();
    }

    @Override
    public Optional<Topic> findOne(Long primaryKey) {
        return Optional.ofNullable(entityManager.find(Topic.class, primaryKey));
    }
    
    @Override
    public Optional<Boolean> edit(Topic updateEntity) {
        return Optional.empty();
    }
    
    @Override
    public Stream<Topic> findAll() {
        return Stream.empty();
    }
    
    @Override
    public long count() {
        return -1L;
    }
    /*
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public long pushTopic(Topic entity, Album album) {
        entityManager.persist(entity);
        entityManager.flush();
        if (entity.getId() <= 0) {
            return -1L;
        }
        
        TopicReplica tr = Optional.of(entity).map(TopicReplica.copyTopic).orElseGet(TopicReplica::new);
        if(null != posts){
            posts.setTopicId(entity.getId());
            postsDao.save(posts);
            tr.copyContent(posts);
        }
        if(null != album){
            album.setTopicId(entity.getId());
            //
            albumDao.cascadesave(album, album.getPictures());
            if(album.getId()>0){
                //更新话题的像册ID
                editAlbum(entity.getId(), album.getId());
            }
            //tr.setAlbum(album);
        }
        //return Optional.of(tr);
        return entity.getId();
    }*/
    
    /**
     * [NativeSQL]
     * @param memberId
     * @param pageable
     * @return 
     *
    @Override
    public Page<Topic> findAllByMemberReply(long memberId, Pageable pageable) {
        final long total = findAllByMemberReplyCount(memberId);
        if (total == 0) {
            return emptyResult();
        }
        final String SQL = "SELECT t.* FROM apo_topic AS t WHERE t.MEMBERID != ?1 AND EXISTS(SELECT 1 FROM apo_topic_posts AS tp WHERE tp.MEMBERID = ?2 AND tp.TOPICID = t.ID) ORDER BY t.RANKINGDATETIME DESC";
        Query query = entityManager.createNativeQuery(SQL, Topic.class).setParameter(1, memberId).setParameter(2, memberId);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        @SuppressWarnings("unchecked")
        final Stream<Topic> result = query.getResultStream();
        return new Page<Topic>() {
            @Override
            public long getTotalElements() {
                return total;
            }
            
            @Override
            public Stream<Topic> getResult() {
                return result;
            }
        };
    }*/
    /**
     * [NativeSQL]
     * @param memberId
     * @return 
     *
    @Override
    public long findAllByMemberReplyCount(long memberId) {
        try {
            final String SQL = "SELECT COUNT(*) FROM apo_topic AS t WHERE t.MEMBERID != ?1 AND EXISTS(SELECT 1 FROM apo_topic_posts AS tp WHERE tp.MEMBERID = ?2 AND tp.TOPICID = t.ID)";
            Object rs = entityManager.createNativeQuery(SQL)
                    .setParameter(1, memberId)
                    .setParameter(2, memberId)
                    .getSingleResult();
            if (rs instanceof Long) {
                return (Long) rs;
            }
        } catch (Exception e) {
            //if (logger.isDebugEnabled()) {
            //    logger.debug("[findAllByMemberReplyCount][TopicDao]", e);
            //}
        }
        return 0L;
    }*/
    /*
    @Override
    public Optional<Topic> findOneArticleForTerm(long termId) {
        try {
            Topic t = entityManager.createQuery("SELECT t FROM Topic t WHERE t.boardId = ?1 ORDER BY t.entryDateTime ASC", Topic.class)
                    .setParameter(1, termId)
                    .setMaxResults(1)
                    .getSingleResult();
            return Optional.ofNullable(t);
        } catch (javax.persistence.NoResultException e) {
            return Optional.empty();
        }
    }*/
    /**
     * [NativeSQL]
     * @param memberId
     * @param size
     * @return 
     */
    @Override
    @SuppressWarnings("unchecked")
    public Stream<Topic> findAllByMemberReply(long memberId, int size) {
        final String SQL = "SELECT t.* FROM apo_topic AS t WHERE t.summary IS NOT NULL AND t.MEMBERID != ?1 AND EXISTS(SELECT 1 FROM apo_topic_posts AS tp WHERE tp.MEMBERID = ?2 AND tp.TOPICID = t.ID) ORDER BY t.RANKINGDATETIME DESC";
        return entityManager.createNativeQuery(SQL, Topic.class).setParameter(1, memberId).setParameter(2, memberId).setMaxResults(size).getResultStream();
    }
}